 insert overwrite table jms_dm.dm_sqs_problem_reply_time_cnt_dt partition(dt)
 select 
    create_date  ,            --创建日期
    network_id  , --登记网点id
    network_code, --登记网点code
    network_name, --登记网点name
    network_parent_name, --父级网点名称
    manage_code, --大区code
    manage_name, --大区name
    agent_code,  --代理区code
    agent_name,  --代理区code
    in_out_bound , --进出港1 进港 2 出港
    order_source_name , --订单来源
    count(1) as total ,--合计问题件票数
    count(1)-count(receiver_reply_time) as no_reply_total ,---未回复票数
    count(if(reply_minutes<=1,waybill_no,null))              as all_time1, --时段1,1H以内（含1小时）
    count(if(reply_minutes>1 and reply_minutes<=4,1,null))   as all_time2, --时段2,1-4小时（含4小时，不含1小时
    count(if(reply_minutes>4 and reply_minutes<=24,1,null))  as all_time3, --时段3,4-24小时（含24小时，不含4小时）
    count(if(reply_minutes>24 and reply_minutes<=72,1,null)) as all_time4, --时段4,24-72小时（含72小时，不含24小时）
    count(if(reply_minutes>72 ,1,null))  as all_time5,  --时段5,超72小时（不含72小时）
    count(if(need_reply=1,1,null)) as need_reply_total ,--需要回复_合计问题件票数
    count(if(need_reply=1 and receiver_reply_time is null,1,null)) as need_no_reply_total ,--需要回复_未回复票数
    count(if(need_reply=1 and reply_minutes<=1,waybill_no,null))   as need_reply_time1,    --需要回复_时段1,1H以内（含1小时）
    count(if(need_reply=1 and reply_minutes>1  and reply_minutes<=4,waybill_no,null))  as need_reply_time2, --需要回复_时段2,1-4小时（含4小时，不含1小时））
    count(if(need_reply=1 and reply_minutes>4  and reply_minutes<=24,waybill_no,null)) as need_reply_time3, --需要回复_时段3,4-24小时（含24小时，不含4小时）
    count(if(need_reply=1 and reply_minutes>24 and reply_minutes<=72,waybill_no,null)) as need_reply_time4, --需要回复_时段4,24-72小时（含72小时，不含24小时）
    count(if(need_reply=1 and reply_minutes>72 ,waybill_no,null))  as need_reply_time5, --需要回复_时段5,超72小时（不含72小时）
    network_type,
    select_type,
    date_time,
    dt
 from 
 (
 select 
    waybill_no ,
    date(create_time) as  create_date  ,
    receive_network_id  as network_id,
    receive_network_code as network_code ,
    receive_network_name as network_name  ,
    receive_network_parent_name as  network_parent_name ,
    receive_network_mr_code as manage_code,
    receive_network_mr_name as manage_name,
    receive_network_proxy_code  as agent_code,
    receive_network_proxy_name  as agent_name,
    in_out_bound ,
    order_source_name ,
    receiver_reply_time,
    if(need_reply=1,1,0) as need_reply, --是否需要回复
    if(reply_minutes is null ,null,cast(reply_minutes/60 as decimal(16,4))) as reply_minutes,  ---回复时长
    1 as select_type ,
    rec_network_type as network_type,
    date_time,
    dt
from  jms_dm.dm_sqs_registration_problem_detail_dt
where dt between date_add('{{ execution_date  | cst_ds }}',-29) and '{{ execution_date  | cst_ds }}'
union all 
select 
    waybill_no ,
    date(create_time) as  create_date  ,
    registration_network_id as network_id,
    registration_network_code as network_code,
    registration_network_name as network_name,
    registration_network_parent_name as  network_parent_name,
    reg_network_mr_code as manage_code,
    reg_network_mr_name as manage_name,
    reg_network_proxy_code as agent_code,
    reg_network_proxy_name as agent_name,
    in_out_bound ,
    order_source_name ,
    receiver_reply_time,
    if(need_reply=1,1,0) as need_reply, --是否需要回复
    if(reply_minutes is null ,null,cast(reply_minutes/60 as decimal(16,4))) reply_minutes,  ---回复时长
    2 as select_type ,
    network_type,
    date_time,
    dt
from  jms_dm.dm_sqs_registration_problem_detail_dt
where dt between date_add('{{ execution_date  | cst_ds }}',-29) and '{{ execution_date  | cst_ds }}'
)t group by 
    create_date   ,
    network_id ,
    network_code ,
    network_name ,
    network_parent_name ,
    manage_code ,
    manage_name ,
    agent_code ,
    agent_name ,
    in_out_bound ,
    order_source_name ,
    select_type,
    network_type,
    date_time,
    dt
distribute by 1;
    